﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using ReportLibrary;

namespace ReportLibrary
{
    class JASEDM
    {

        static bool checkFile(string filename)
        {

            SqlConnection con = new SqlConnection("Data Source=172.29.163.17;Initial Catalog=jasedm;Persist Security Info=True;User ID=jasedm;Password=jasedm");
            con.Open();
            SqlCommand cmd = new SqlCommand("select filename from TJASEDM_DocumentManagement where filename = '" + filename + "'");
            using (cmd.Connection = con)
            {
                SqlDataReader reader;
                using (reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
        }

        public static int dbFileSave(string filePath, string docSource, string docRef,
                                string docDesc, string docType, string createdBy)
        {

            int filesCreated = 0;
            SqlConnection con = new SqlConnection("Data Source=172.29.163.17;Initial Catalog=jasedm;Persist Security Info=True;User ID=jasedm;Password=jasedm");
            con.Open();

            try
            {
                
                FileInfo fi = new FileInfo(filePath);


                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                byte[] buff = br.ReadBytes((int)fs.Length);

                string docsource = docSource;
                string docref = docRef;
                string docdesc = docDesc;
                string doctype = docType;
                DateTime createdon = fi.CreationTime.Date.ToUniversalTime();
                DateTime changedon = fi.LastWriteTime.Date.ToUniversalTime();

                // SQL Parameters

                //FileCreationDate
                //Kavita 02/07/2012 --Start
                //SqlParameter FileCreationDate = new SqlParameter("@FileCreationDate", System.Data.SqlDbType.DateTime);
                //FileCreationDate.Value = createdon;
                ////FileLastWrite
                //SqlParameter FileLastWrite = new SqlParameter("@FileLastWrite", System.Data.SqlDbType.DateTime);
                //FileLastWrite.Value = changedon;
                ////MimeType
                //SqlParameter mimetype = new SqlParameter("@mimetype", System.Data.SqlDbType.VarChar);
                //mimetype.Value = MiMEType.GetMIMEType(filePath).ToString();
                //// File Content (Binary)
                //SqlParameter filecontent = new SqlParameter("@filecontent", System.Data.SqlDbType.VarBinary);
                //filecontent.Value = buff;
                //// Filename
                //SqlParameter filename = new SqlParameter("@filename", System.Data.SqlDbType.VarChar);
                //filename.Value = fi.Name.ToString();
                //// File Extension
                //SqlParameter fileextension = new SqlParameter("@fileextension", System.Data.SqlDbType.NChar);
                //fileextension.Value = fi.Extension.ToString();
                //// DocumentDescription
                //SqlParameter documentdescription = new SqlParameter("@documentdescription", System.Data.SqlDbType.VarChar);
                //documentdescription.Value = docdesc.ToString();
                //// DocumentType
                //SqlParameter DocumentType = new SqlParameter("@DocumentType", System.Data.SqlDbType.VarChar);
                //DocumentType.Value = doctype.ToString();
                //// DocumentRefence
                //SqlParameter DocumentRefence = new SqlParameter("@DocumentRefence", System.Data.SqlDbType.VarChar);
                //DocumentRefence.Value = docref.ToString();
                //// DocumentSource
                //SqlParameter DocumentSource = new SqlParameter("@DocumentSource", System.Data.SqlDbType.NChar);
                //DocumentSource.Value = docsource.ToString();
                //// CreatedBy
                //SqlParameter CreatedBy = new SqlParameter("@CreatedBy", System.Data.SqlDbType.NChar);
                //CreatedBy.Value = CreatedBy;
                ///Kavita -- end
                ///


                
                SqlCommand cmd = new SqlCommand("uspUploadDocument", con);
                cmd.CommandType =  System.Data.CommandType.StoredProcedure;
              

                //MimeType
                SqlParameter mimetype = new SqlParameter("@MIMEType", System.Data.SqlDbType.VarChar);
                mimetype.Value = MiMEType.GetMIMEType(filePath).ToString();
                
                // File Content (Binary)
                SqlParameter filecontent = new SqlParameter("@FileData", System.Data.SqlDbType.VarBinary);
                filecontent.Value = buff;

                // CreatedBy
                SqlParameter CreatedBy = new SqlParameter("@LoginID", System.Data.SqlDbType.NChar,60);
                CreatedBy.Value = CreatedBy.ToString();

                // Filename
                SqlParameter filename = new SqlParameter("@FileName", System.Data.SqlDbType.VarChar,350);
                filename.Value = fi.Name.ToString();

                // File Extension
                SqlParameter fileextension = new SqlParameter("@FileExtension", System.Data.SqlDbType.NChar,10);
                fileextension.Value = fi.Extension.ToString();

                SqlParameter FileCreationDate = new SqlParameter("@FileCreateDate", System.Data.SqlDbType.DateTime);
                FileCreationDate.Value = createdon;


                //FileLastWrite
                SqlParameter FileLastWrite = new SqlParameter("@FileLastWriteDate", System.Data.SqlDbType.DateTime);
                FileLastWrite.Value = changedon;

                // DocumentDescription
                SqlParameter documentdescription = new SqlParameter("@Description", System.Data.SqlDbType.VarChar,350);
                documentdescription.Value = docdesc.ToString();
                // DocumentType
                SqlParameter DocumentType = new SqlParameter("@Type", System.Data.SqlDbType.VarChar,100);
                DocumentType.Value = doctype.ToString();
                // DocumentRefence
                SqlParameter DocumentRefence = new SqlParameter("@Ref", System.Data.SqlDbType.VarChar,100);
                DocumentRefence.Value = docref.ToString();
                // DocumentSource
                SqlParameter DocumentSource = new SqlParameter("@Source", System.Data.SqlDbType.NChar,100);
                DocumentSource.Value = docsource.ToString();
                

           
                cmd.Parameters.Add(mimetype);
                cmd.Parameters.Add(filecontent);
                cmd.Parameters.Add(CreatedBy);
                cmd.Parameters.Add(filename);
                cmd.Parameters.Add(fileextension);
                cmd.Parameters.Add(FileCreationDate);
                cmd.Parameters.Add(FileLastWrite);
                cmd.Parameters.Add(documentdescription);
                cmd.Parameters.Add(DocumentType);
                cmd.Parameters.Add(DocumentRefence);
                cmd.Parameters.Add(DocumentSource);
                

                try
                {
                    filesCreated = cmd.ExecuteNonQuery();
                    br.Close();
                    fs.Close();
                    con.Close();
                   
                }
                catch (Exception e)
                {
                    //SqlConnection conerror = new SqlConnection("Data Source=172.29.163.17;Initial Catalog=jasedm;Persist Security Info=True;User ID=jasedm;Password=jasedm");
                    //conerror.Open();
                    //SqlCommand cmderror = new SqlCommand("insert into TJASEDM_DocumentManagement_error (filename, errortype) values ('" + file + "','" + e.Message.ToString().Replace("'", "") + "')");
                    //cmderror.Connection = conerror;
                    //cmderror.ExecuteNonQuery();
                    //conerror.Close();
                    //br.Close();
                    //fs.Close();
                    //con.Close();

                    Console.Write(e.Message.ToString());
                }

            }

            catch (Exception eee)
            {
                //SqlConnection conerror = new SqlConnection("Data Source=172.29.163.17;Initial Catalog=jasedm;Persist Security Info=True;User ID=jasedm;Password=jasedm");
                //conerror.Open();
                //SqlCommand cmderror = new SqlCommand("insert into TJASEDM_DocumentManagement_error (filename, errortype) values ('" + file + "','" + eee.Message.ToString().Replace("'", "") + "')");
                //cmderror.Connection = conerror;
                //cmderror.ExecuteNonQuery();
                //conerror.Close();
                //con.Close();

                Console.Write(eee.Message.ToString());
            }

            return filesCreated; 
        }

        
    }
}
